﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Chire.wechat;

namespace Chire.ChireInter.News
{
    public class News_Action
    {

        // 1. 获取消息类型列表
        public List<News_Type_Model> getNewsTypeList()
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "select * from news_type";
            //查询音乐信息
            DataTable dt = new DataTable();

            SqlDataAdapter dap = new SqlDataAdapter(strselect, sqlcon);
            dap.Fill(dt);
            int rows = dt.Rows.Count;

            List<News_Type_Model> newstypeList = new List<News_Type_Model>();
            for (int i = 0; i < rows; i++)
            {
                News_Type_Model newsTypeModel = new News_Type_Model();
                newsTypeModel.id = dt.Rows[i]["id"].ToString();
                newsTypeModel.name = dt.Rows[i]["name"].ToString();
                newsTypeModel.createtime = dt.Rows[i]["createtime"].ToString();
                newsTypeModel.linkId = dt.Rows[i]["linkId"].ToString();
                newsTypeModel.ablum = dt.Rows[i]["ablum"].ToString();
                newstypeList.Add(newsTypeModel);
            }
            sqlcon.Close();


            return newstypeList;
        }

        // 2. 添加消息类型
        public void addNewsInfoManager(string info,string ablum){
             // 连接数据库
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();
            string StrInsert = "";

            StrInsert = "insert into news_type(name,createtime,ablum,linkId) values(@name,@createtime,@ablum,@linkId)";
            SqlCommand cmd = new SqlCommand(StrInsert, sqlcon);
            // 添加参数并且设置参数值

            cmd.Parameters.Add("@name", SqlDbType.VarChar, 350);
            cmd.Parameters["@name"].Value = info;
            cmd.Parameters.Add("@createtime", SqlDbType.VarChar, 50);
            cmd.Parameters["@createtime"].Value = DateTime.Now.ToString() ;
            cmd.Parameters.Add("@linkId", SqlDbType.VarChar, 350);
            cmd.Parameters["@linkId"].Value = Constance.Instance.getRandomStr(11);
            cmd.Parameters.Add("@ablum", SqlDbType.VarChar, 350);
            cmd.Parameters["@ablum"].Value = ablum;

            // 执行插入数据的操作
            cmd.ExecuteNonQuery();
            sqlcon.Close();
        }

        // 3. 获取消息
        public List<News_Model> getNewsList(string type, int page, int size)
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();
            int pageWithIndex = ((page - 1) * size) + 1;
            int maxPageIndex = pageWithIndex + size - 1;
            string strselect = "select a.* from(select row_number() over (order by id desc) as rn,* from news_list) a where type ='"+type+"' and rn between '" + pageWithIndex + "' and '" + maxPageIndex + "'";

            //查询音乐信息
            DataTable dt = new DataTable();

            SqlDataAdapter dap = new SqlDataAdapter(strselect, sqlcon);
            dap.Fill(dt);
            int rows = dt.Rows.Count;

            List<News_Model> newsList = new List<News_Model>();
            for (int i = 0; i < rows; i++)
            {
               
                string id = dt.Rows[i]["id"].ToString();
                string name = dt.Rows[i]["name"].ToString();
                string des = dt.Rows[i]["des"].ToString();
                string typeInfo = dt.Rows[i]["type"].ToString();
                string ablum = dt.Rows[i]["ablum"].ToString();
                string link = dt.Rows[i]["link"].ToString();
                string datetime = dt.Rows[i]["createtime"].ToString();
                News_Model newsModel = new News_Model()
                {
                    id = id,
                    name = name,
                    des = des,
                    type = typeInfo,
                    ablum = ablum,
                    link = link,
                    createtime = datetime
                };
                newsList.Add(newsModel);
            }
            sqlcon.Close();
            return newsList;
        }

    }
}